/**
 * 2018年11月27日下午9:21:38
 * @author 谢进财
 */
package com.baihang.project.ds.service;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;

import com.baihang.framework.dao.Record;
import com.baihang.framework.kit.DownLoadKit;
import com.baihang.framework.kit.ExcelKit;
import com.baihang.framework.kit.JsonKit;
import com.baihang.framework.kit.ModelKit;
import com.baihang.framework.kit.PathKit;
import com.baihang.framework.kit.database.RedisKit;
import com.baihang.framework.service.BaseService;
import com.baihang.project.ds.common.RemoteInvocationKit;
import com.baihang.project.ds.dao.CustomerOneDao;
import com.baihang.project.ds.model.TUserModel;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import redis.clients.jedis.Jedis;

@Service
public class CustomerOneService extends BaseService {
	private static Logger logger = LoggerFactory.getLogger(CustomerOneService.class);
	@Autowired
	private CustomerOneDao dao;
	private Jedis jedis = RedisKit.getJedis();
	public String getGrid1(int page,int pagesize,String sortname,String sortorder) {
		int count = dao.queryForInt("select count(*) from t_user where D_Source=1");
		
		List<Record> list = dao.queryForRecordList(String.format("select * from t_user where D_Source=1 order by %s %s limit %d,%d ", sortname,sortorder,(page-1)*pagesize,pagesize));
		//从redis中读取 在线 认筹 价格  锁定
		//在线
		/*Set<String> zaixian =jedis.smembers("zaixian");//在线
		Set<String> renchou =jedis.smembers("renchou");//认筹
		Set<String> jiage =jedis.smembers("jiage");//价格
		Set<String> suoding =jedis.smembers("suoding");//锁定*/		
		for(Record rec : list) {
			String D_Tel = rec.getStr("D_Tel");
			if(jedis.sismember("zaixian", D_Tel)) {
				rec.getColumns().put("D_Online", 1);
			}else {
				rec.getColumns().put("D_Online", 0);
			}
			if(jedis.hexists("rengou", D_Tel)) {
				rec.getColumns().put("Seatcount", 1);
			}else {
				rec.getColumns().put("Seatcount", 0);
			}
			if(jedis.sismember("jiage", D_Tel)) {
				rec.getColumns().put("D_PriceType", 1);
			}else {
				rec.getColumns().put("D_PriceType", 0);
			}
			if(jedis.sismember("suoding", D_Tel)) {
				rec.getColumns().put("D_Lock", 1);
			}else {
				rec.getColumns().put("D_Lock", 0);
			}
		}
		String jsonRecord = JsonKit.listToJson(list, 1);
		StringBuilder sb = new StringBuilder("{");
		sb.append("\"Rows\":"+jsonRecord+",");
		sb.append("\"Total\":"+count);
		sb.append("}");
		return sb.toString();
	}
	
	public String getGrid2(int page,int pagesize,String sortname,String sortorder) {
		String result="" ;
		if(RedisKit.getJedis().hmget("systemConfig","LoginTest").get(0).equals("1")) {
			Jedis jedis = RedisKit.getJedis();
			long count = jedis.scard("zaixian");
			Set<String> zaixian =jedis.smembers("zaixian");
			List<Record> list = new ArrayList<Record>();
			for(String user:zaixian) {
				Record rec = new Record();
				rec.getColumns().put("D_Online", 1);
				rec.getColumns().put("D_Online", 1);
				if(jedis.hexists("rengou", user)) {
					rec.getColumns().put("Seatcount", 1);
				}else {
					rec.getColumns().put("Seatcount", 0);
				}			
				if(jedis.sismember("suoding", user)) {
					rec.getColumns().put("D_Lock", 1);
				}else {
					rec.getColumns().put("D_Lock", 0);
				}
				rec.getColumns().put("D_Tel", user);
				list.add(rec);
			}
			String jsonRecord = JsonKit.listToJson(list, 1);
			StringBuilder sb = new StringBuilder("{");
			sb.append("\"Rows\":"+jsonRecord+",");
			sb.append("\"Total\":"+count);
			sb.append("}");
			result =sb.toString();
		}else {			
			int count = dao.queryForInt("select count(*) from t_user where D_Source=0");		
			List<Record> list = dao.queryForRecordList(String.format("select * from t_user where D_Source=0 order by %s %s limit %d,%d ", sortname,sortorder,(page-1)*pagesize,pagesize));
			Jedis jedis = RedisKit.getJedis();
			for(Record rec : list) {
				String D_Tel = rec.getStr("D_Tel");
				if(jedis.sismember("zaixian", D_Tel)) {
					rec.getColumns().put("D_Online", 1);
				}else {
					rec.getColumns().put("D_Online", 0);
				}
				if(jedis.hexists("rengou", D_Tel)) {
					rec.getColumns().put("Seatcount", 1);
				}else {
					rec.getColumns().put("Seatcount", 0);
				}
				if(jedis.sismember("jiage", D_Tel)) {
					rec.getColumns().put("D_PriceType", 1);
				}else {
					rec.getColumns().put("D_PriceType", 0);
				}
				if(jedis.sismember("suoding", D_Tel)) {
					rec.getColumns().put("D_Lock", 1);
				}else {
					rec.getColumns().put("D_Lock", 0);
				}
			}
			String jsonRecord = JsonKit.listToJson(list, 1);
			StringBuilder sb = new StringBuilder("{");
			sb.append("\"Rows\":"+jsonRecord+",");
			sb.append("\"Total\":"+count);
			sb.append("}");
			result =sb.toString();
		}
		return result;
	}
	
	public ResponseEntity<byte[]> downloadCustomerOne(int type) throws Exception{
		String sql = String.format("select D_Name as 姓名, D_Tel as 手机,D_Card as 身份证号,D_Saller as 置业顾问,D_SallerTel as 置业顾问手机,D_lastLoginTime as 最后登入时间  from t_user where D_Source=%d",type);
		
		List<Record> records = dao.queryForRecordList(sql);	
		if(records==null || records.size()==0) {
		    	return null;
		}else {
			//收藏集合
			List<Record> userScList= dao.queryForRecordList("select r.D_ID,r.D_BanName,r.D_RoomNo,r.D_Unit,t.UserName,t.Id,t.Sort from t_collection as t LEFT JOIN room as r on t.FieldId=r.D_ID");
			if(userScList.size()>0) {
				for(Record rec:records) {//读取一个用户
					for(Record rsc:userScList) {//读取所有收藏
						if(rec.getStr("手机").equals(rsc.getStr("UserName"))) {							
							rec.getColumns().put(String.format("收藏%d", rsc.getInt("Sort")), String.format("%s-%s-%s", rsc.getStr("D_BanName"),rsc.getStr("D_Unit"),rsc.getStr("D_RoomNo")));													
						}
					}
				}
			}/*else {
				return null;
			}*/
		}
		
		String filePath = PathKit.getWebRootPath()+"/excelTemple/用户登录数据.xls";
		File file = new File(filePath);
		HSSFWorkbook workbook = writeToExcel(records,file);
				
		return DownLoadKit.exportExcel(workbook, type==0?"认筹帐号":"管理帐号");
	}
	public  HSSFWorkbook  writeToExcel(List<Record> records,File file) throws Exception {
		FileInputStream fs=new FileInputStream(file); //读取文件  
        POIFSFileSystem ps=new POIFSFileSystem(fs);//使用POI提供的方法得到excel的信息
        HSSFWorkbook wb=new HSSFWorkbook(ps);  
        HSSFSheet sheet=wb.getSheetAt(0);  //获取到工作表，因为一个excel可能有多个工作表  
            //创建数据行
            /***因为只有第一行有数据，下面的行没有数据，所以下面的行都需要 createRow****/
      		Record rec=null;
      		for(int k=0;k<records.size();k++) {
      			 rec = records.get(k);     			
      			 HSSFRow row = sheet.createRow(k+1); //从第二行开始
      			 HSSFCell cell = row.createCell(0);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("姓名"));
      			 
      			 cell = row.createCell(1);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("手机"));
      			 
      			 cell = row.createCell(2);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("身份证号"));
      			 
      			 cell = row.createCell(3);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("置业顾问"));
      			 
      			 cell = row.createCell(4);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("置业顾问手机"));
      			 
      			 cell = row.createCell(5);
      			 cell.setCellType(CellType.STRING);//字符型
      			 if(rec.getColumns().containsKey("收藏1")) {
      				 cell.setCellValue(rec.getStr("收藏1"));      				 
      			 }
      			 
      			 cell = row.createCell(6);
      			 cell.setCellType(CellType.STRING);//字符型
      			 if(rec.getColumns().containsKey("收藏2")) {
      				 cell.setCellValue(rec.getStr("收藏2"));      				 
      			 }
      			 
      			 cell = row.createCell(7);
      			 cell.setCellType(CellType.STRING);//字符型
      			 if(rec.getColumns().containsKey("收藏3")) {
      				 cell.setCellValue(rec.getStr("收藏3"));      				 
      			 }
      			 
      			 cell = row.createCell(8);
      			 cell.setCellType(CellType.STRING);//字符型
      			 if(rec.getColumns().containsKey("收藏4")) {
      				 cell.setCellValue(rec.getStr("收藏4"));      				 
      			 }
      			 
      			 cell = row.createCell(9);
      			 cell.setCellType(CellType.STRING);//字符型
      			 if(rec.getColumns().containsKey("收藏5")) {
      				 cell.setCellValue(rec.getStr("收藏5"));      				 
      			 }
      			 
      			 cell = row.createCell(10);
      			 cell.setCellType(CellType.STRING);//字符型
      			 cell.setCellValue(rec.getStr("最后登入时间"));
      			      			
      		}
		return wb;
	}
	
	public String readCustomerOneByExcel(String fileName) {
		String filePath = PathKit.getWebRootPath()+"/upload/"+fileName;
		System.err.println(filePath);
		File file = new File(filePath);
		 Map<Integer,String> keyMap = new HashMap<Integer,String>();			
		 keyMap.put(0, "D_HouseName");
		 keyMap.put(1, "D_Name");
		 keyMap.put(2, "D_Sex");
		 keyMap.put(3, "D_Tel");
		 keyMap.put(4, "D_Card");
		 keyMap.put(5, "D_FromType");
		 keyMap.put(6, "D_OperationName");
		 keyMap.put(7, "D_OperationDate");
		 keyMap.put(8, "D_lastLoginTime");		
		 List<Record> recList = new ArrayList<Record>();
		try {
			recList = ExcelKit.readFromExcelForString(file,keyMap);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		/*for(Record rec:recList) {
			rec.getColumns().put("D_BanName", fileName.substring(0, fileName.indexOf(".")));
		}*/
		 String json =String.format("{\"Rows\":%s}", JsonKit.listToJson(recList, 1));
		 //System.err.println(json);
		return json;
	}
	
	public boolean addGrid(String json,int source) {
		JSONArray jsonArray = JSONArray.fromObject(json); 
		try {
			dao.batchInsertCustomerOneList(jsonArray, source);
		}catch (Exception e) {
			logger.info(e.getMessage());
			return false;
		}
		return true;
	}
	
	public String deleteCustomerOne(String tel) {
		String result ="true";
		String msg ="变更成功";
		if(jedis.sismember("zaixian", tel)){
			result="false";
			msg="用户在线禁止删除";
			return JsonKit.toDoubleJson(result, msg);
		}
		String sql =String.format("delete from t_user  where D_Tel='%s'", tel);
		
		int result2 = dao.delete(sql);			
		if(result2>0) {
			return JsonKit.toDoubleJson(result, msg);
		}else {
			result="false";
			msg="删除失败";
			return JsonKit.toDoubleJson(result, msg);
		}
		
	}
	public boolean deleteCustomerOneAll(String source) {
		
		String sql =String.format("delete from t_user  where D_Source=%d", source);
		
		int result = dao.delete(sql);			
		if(result>0) {
			return true;
		}
		return false;
	}
	public boolean saveCustomerOne(String json) {
		JSONObject jsonObject = JSONObject.fromObject(json);
		TUserModel tuser = new TUserModel();
		tuser.setD_Card(jsonObject.getString("D_Card"));
		tuser.setD_FromType(jsonObject.getString("D_FromType"));
		tuser.setD_Name(jsonObject.getString("D_Name"));
		tuser.setD_Sex(jsonObject.getString("D_Sex"));
		tuser.setD_Source(jsonObject.getString("D_Source"));
		tuser.setD_Tel(jsonObject.getString("D_Tel"));
		try {
			dao.save(ModelKit.modelToSaveSql(tuser));
		}catch (Exception e) {
			logger.info(e.getMessage());
			return false;
		}
		return true;
	}
	public String updateCustomerOne(String json) {
		JSONObject jsonObject = JSONObject.fromObject(json);
		String result ="true";
		String msg ="变更成功";
		if(jedis.sismember("zaixian", jsonObject.getString("tel"))){
			result="false";
			msg="用户在线禁止修改";
			return JsonKit.toDoubleJson(result, msg);
		}
		try {
			dao.update(String.format("update t_user set D_Tel=%s where D_Tel=%s", jsonObject.getString("newTel"),jsonObject.getString("tel")));
		}catch (Exception e) {
			logger.info(e.getMessage());
			result="false";
			msg="变更失败";
			return JsonKit.toDoubleJson(result, msg);
		}
		return JsonKit.toDoubleJson(result, msg);
	}
	
	public boolean changeShowPrice(int state , String tel) {
		Jedis jedis = RedisKit.getJedis();
		if(state ==0) {//删除
			jedis.srem("jiage", tel);
		}else {//插入
			jedis.sadd("jiage", tel);
		}
		return true;
	}
	
	public boolean changeUserLock(int state , String tel) {
		Jedis jedis = RedisKit.getJedis();
		if(state ==0) {//删除
			jedis.srem("suoding", tel);
		}else {//插入
			jedis.sadd("suoding", tel);
		}
		return true;
	}
	
	public boolean userGetOut(String tel) {
		Jedis jedis = RedisKit.getJedis();
		jedis.srem("zaixian", tel);//删除在线状态
		return RemoteInvocationKit.userGetOut(tel);
	}
}
